-- EXEC SP_CALCULATE_LOT_COST
alter PROC SP_CALCULATE_LOT_COST 
AS
BEGIN
	
	DECLARE @LOT_ID			BIGINT
	DECLARE @PROCESSSTEP_ID 	BIGINT
	DECLARE @TR_PROCESSSTEP_ID 	BIGINT
	DECLARE @PS_TYPE		VARCHAR(50) 
	DECLARE @DATE_IN		DATETIME
	DECLARE @QTY_IN			INT
	DECLARE @QUARTER_NO		INT
	DECLARE @YEAR		INT
	DECLARE @WAFER_QTY_IN		INT
	DECLARE @Q1_CUMM_STD_COST	NUMERIC(10,4)
	DECLARE @Q2_CUMM_STD_COST	NUMERIC(10,4)
	DECLARE @Q3_CUMM_STD_COST	NUMERIC(10,4)
	DECLARE @Q4_CUMM_STD_COST	NUMERIC(10,4) 

	SELECT @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR 
	WHERE CURRENT_TIMESTAMP >= CQC_START_DATE AND CURRENT_TIMESTAMP <= CQC_END_DATE	

	SELECT @YEAR = YEAR(CURRENT_TIMESTAMP)

	print @YEAR
	
	DECLARE LOT_CURSOR CURSOR FOR 
 	SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, WAFER_QTY_IN, QTY_IN, PS_TYPE FROM LOT_TABLE LT, PROCESS_STEP PS
	WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_CURRENT_STATUS != 'Close'
	union
	SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, WAFER_QTY_IN, QTY_IN, PS_TYPE FROM SHIPPED_LOT SL,LOT_TABLE LT, PROCESS_STEP PS
	WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND STATUS != 'CLOSE'

	OPEN LOT_CURSOR
	FETCH NEXT FROM LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @WAFER_QTY_IN, @QTY_IN, @PS_TYPE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @PS_TYPE = 'FAB' OR @PS_TYPE = 'WAFER BANK' OR @PS_TYPE = 'SORT')
		BEGIN
			IF @QUARTER_NO = 1
			BEGIN
				SELECT @Q1_CUMM_STD_COST = Q1_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q1_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @WAFER_QTY_IN * ISNULL( @Q1_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
			END
			ELSE IF @QUARTER_NO = 2
			BEGIN
				SELECT @Q2_CUMM_STD_COST = Q2_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q2_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @WAFER_QTY_IN * ISNULL( @Q2_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
			END
			ELSE IF @QUARTER_NO = 3
			BEGIN
				SELECT @Q3_CUMM_STD_COST = Q3_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q3_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @WAFER_QTY_IN * ISNULL( @Q3_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
			END
			ELSE IF @QUARTER_NO = 4
			BEGIN
				SELECT @Q4_CUMM_STD_COST = Q4_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q4_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @WAFER_QTY_IN * ISNULL( @Q4_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID						
			END			
		END
		ELSE 
		BEGIN
			IF @QUARTER_NO = 1
			BEGIN
				SELECT @Q1_CUMM_STD_COST = Q1_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q1_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @QTY_IN * ISNULL( @Q1_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
			END
			ELSE IF @QUARTER_NO = 2
			BEGIN
				SELECT @Q2_CUMM_STD_COST = Q2_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q2_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @QTY_IN * ISNULL( @Q2_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
			END
			ELSE IF @QUARTER_NO = 3
			BEGIN
				SELECT @Q3_CUMM_STD_COST = Q3_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q3_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @QTY_IN * ISNULL( @Q3_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
			END
			ELSE IF @QUARTER_NO = 4
			BEGIN
				SELECT @Q4_CUMM_STD_COST = Q4_STANDARD_COST FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				UPDATE LOT_TABLE SET LOT_COST = @Q4_CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @QTY_IN * ISNULL( @Q4_CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
			END			
		END

		SET @Q1_CUMM_STD_COST	= NULL 
		SET @Q2_CUMM_STD_COST	= NULL 
		SET @Q3_CUMM_STD_COST	= NULL 
		SET @Q4_CUMM_STD_COST	= NULL 
		SET @LOT_ID		= NULL		
		SET @PROCESSSTEP_ID 	= NULL
		SET @WAFER_QTY_IN	= NULL
		SET @QTY_IN		= NULL
		SET @PS_TYPE		= NULL

		FETCH NEXT FROM LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @WAFER_QTY_IN, @QTY_IN, @PS_TYPE
	END	
	CLOSE LOT_CURSOR
	DEALLOCATE LOT_CURSOR
END 